// repository/RmcRecordRepository.java
package com.cscec5b.inspection.repository;

import com.cscec5b.inspection.entity.RmcRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface RmcRecordRepository extends JpaRepository<RmcRecord, Long> {

    // 1) 按项目分页/排序（也可用 Pageable）
    List<RmcRecord> findByProjectIdOrderByIdDesc(Long projectId);

    // 2) 原生 SQL + JSON_CONTAINS 示例（查包含某车号的记录）
    @Query(value = """
        SELECT * FROM rmc_construction_record r
        WHERE r.truck_nos IS NOT NULL
          AND JSON_CONTAINS(r.truck_nos, JSON_QUOTE(:truckNo), '$')
        ORDER BY r.id DESC
        """, nativeQuery = true)
    List<RmcRecord> findByTruckNo(@Param("truckNo") String truckNo);

    // 3) 原生 SQL：按检查项关键字模糊（在 checklist JSON 中找 title 或 result）
    @Query(value = """
        SELECT * FROM rmc_construction_record r
        WHERE r.checklist IS NOT NULL
          AND JSON_SEARCH(r.checklist, 'one', CONCAT('%',:kw,'%'), NULL, '$[*].title') IS NOT NULL
        ORDER BY r.id DESC
        """, nativeQuery = true)
    List<RmcRecord> searchByChecklistTitle(@Param("kw") String keyword);
}
